﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;
using System.Data;
using OfficeOpenXml;
using System.IO;
using Atom.Entity;  // namespace for the ExcelPackage assembly

namespace Atom.Utility
{
    public class ExcelManager
    {
        protected LogService log;
        public ExcelManager() { log = LogService.Createinstance("SystemBLL"); }
        public string GetWorkBook(string path)
        {
            string str = "";
            //Workbook wb = new Workbook(path);
            //for (int i = 0; i < wb.Sheets.Count; i++)
            //{
            //    Worksheet ws = wb.Sheets[i];

            //    str += ("/r/n" + ws.Name);

            //    if (i == 1)
            //    {


            //        for (uint r = ws.Rows.MinRow; r <= ws.Rows.MaxRow; ++r)
            //        {

            //            Row row = ws.Rows[r];
            //            if (row != null)
            //            {
            //                for (uint c = row.Cells.MinCol; c <= row.Cells.MaxCol; ++c)
            //                {

            //                    Cell cell = row.Cells[(byte)c];
            //                    Console.Write("Col: ");
            //                    Console.Write(c);
            //                    if (cell != null)
            //                    {


            //                        MessageBox.Show(cell.Value == null ? "" : cell.Value.ToString());

            //                    }

            //                }
            //            }

            //        }
            //    }
            //}



            FileInfo newFile = new FileInfo(@"C:\20111.xlsx");
            FileInfo template = new FileInfo(@"C:\2011.xlsx");
            using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
            {

                //xlPackage.Workbook.Worksheets[1].defaultRowHeight = 12;
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[4];
                if (worksheet != null)
                {


                    // ExcelCell cell;
                    const int startRow = 8;
                    int row = startRow;

                    for (int i = row; i < 24; i++)
                    {
                        for (int j = 2; j < 7; j++)
                        {
                            worksheet.Cell(i, j).Value = i.ToString() + j.ToString();
                        }

                    }
                }
                xlPackage.Save();
            }

            return str;

        }

        /// <summary>
        /// 保存并写入Excel
        /// </summary>
        /// <param name="C1entitys"></param>
        /// <param name="C2entitys"></param>
        /// <param name="C3_1Entitys"></param>
        /// <param name="C3_2Entitys"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public bool SaveAndImport(List<C1Entity> C1entitys, List<C2Entity> C2entitys, List<C3_1Entity> C3_1Entitys, List<C3_2Entity> C3_2Entitys, string path)
        {
            bool success = false;
            try
            {
                FileInfo newFile = new FileInfo(path);
                FileInfo template = new FileInfo(Environment.CurrentDirectory + "\\Template\\监测表格.xlsx");

                using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
                {
                    try
                    {
                        SetWorksheetC1(C1entitys, xlPackage.Workbook.Worksheets[2]);
                    }
                    catch (Exception ee)
                    {

                        log.Error("获取监测报表失败C1，详细信息：" + ee.Message); ;
                    }
                    try
                    {
                        SetWorksheetC2(C2entitys, xlPackage.Workbook.Worksheets[3]);
                    }
                    catch (Exception ee)
                    {

                        log.Error("获取监测报表失败C2，详细信息：" + ee.Message); ;
                    } try
                    {
                        SetWorksheetC3_1(C3_1Entitys, xlPackage.Workbook.Worksheets[4]);
                    }
                    catch (Exception ee)
                    {

                        log.Error("获取监测报表失败C31，详细信息：" + ee.Message); ;
                    } try
                    {
                        SetWorksheetC3_2(C3_2Entitys, xlPackage.Workbook.Worksheets[5]);
                    }
                    catch (Exception ee)
                    {

                        log.Error("获取监测报表失败C32，详细信息：" + ee.Message); ;
                    }

                    xlPackage.Save();
                }

                success = true;

            }
            catch(Exception ex)
            {
                log.Error("获取监测报表失败，详细信息：" + ex.Message);
            }


            return success;
        }

        /// <summary>
        /// 设置Worksheet1 C1表
        /// </summary>
        /// <param name="entitys"></param>
        /// <param name="sheet"></param>
        public void SetWorksheetC1(List<C1Entity> entitys, ExcelWorksheet sheet)
        {

            if (sheet != null)
            {



                int startCell = 3;
                int startRow = 23;
                //列循环
                for (int i = startCell; i < entitys.Count + startCell; i++)
                {
                    C1Entity entity = entitys[i - startCell];
                    //行循环

                    sheet.Cell(startRow, i).Value = entity.TotalFee.ToString("F2");
                    sheet.Cell(startRow + 1, i).Value = entity.YPFee.ToString("F2");
                    sheet.Cell(startRow + 2, i).Value = entity.BCNMTotalFee.ToString("F2");
                    sheet.Cell(startRow + 3, i).Value = entity.BCNMYPFee.ToString("F2");
                    sheet.Cell(startRow + 4, i).Value = entity.BCNMZLFee.ToString("F2");
                    sheet.Cell(startRow + 5, i).Value = entity.BCFWTotalFee.ToString("F2");
                    sheet.Cell(startRow + 6, i).Value = entity.BCFWYPFee.ToString("F2");
                    sheet.Cell(startRow + 7, i).Value = entity.BCFWZLFee.ToString("F2");
                    sheet.Cell(startRow + 8, i).Value = entity.XNHBCTotalFee.ToString("F2");
                    sheet.Cell(startRow + 9, i).Value = entity.InhosCount.ToString("F2");
                    sheet.Cell(startRow + 10, i).Value = entity.InhosOperationCount.ToString();
                    sheet.Cell(startRow + 11, i).Value = entity.OuthosBedCount.ToString();


                    sheet.Cell(startRow + 28, i).Value = entity.InhosNormalSCCount.ToString();
                    sheet.Cell(startRow + 32, i).Value = entity.InhosNormalSCCTotalFee.ToString("F2");
                    sheet.Cell(startRow + 36, i).Value = entity.InhosNormalSCXNHBC.ToString("F2");
                    sheet.Cell(startRow + 40, i).Value = entity.InhosOtherSCXNHBC.ToString("F2");




                }
            }



        }

        public void SetWorksheetC2(List<C2Entity> entitys, ExcelWorksheet sheet)
        {
            if (sheet != null)
            {



                int startCell = 2;
                int startRow = 8;
                //列循环
                for (int i = startCell; i < entitys.Count + startCell; i++)
                {
                    C2Entity entity = entitys[i-startCell];
                    //行循环

                    sheet.Cell(startRow, i).Value = entity.InhosCount.ToString("F2");
                    sheet.Cell(startRow + 1, i).Value = entity.InhosBCCount.ToString("F2");
                    sheet.Cell(startRow + 2, i).Value = entity.TotalFee.ToString("F2");
                    sheet.Cell(startRow + 3, i).Value = entity.YPFee.ToString("F2");
                    sheet.Cell(startRow + 4, i).Value = entity.ZLFee.ToString("F2");
                    sheet.Cell(startRow + 5, i).Value = entity.CHNMTotalFee.ToString("F2");
                    sheet.Cell(startRow + 6, i).Value = entity.CHNMYPFee.ToString("F2");
                    sheet.Cell(startRow + 7, i).Value = entity.CHNMZLFee.ToString("F2");
                    sheet.Cell(startRow + 8, i).Value = entity.CHNMBCFWTotalFee.ToString("F2");
                    sheet.Cell(startRow + 9, i).Value = entity.CHNMBCFWYPFee.ToString("F2");
                    sheet.Cell(startRow + 10, i).Value = entity.CHNMBCFWZLFee.ToString("F2");
                    sheet.Cell(startRow + 11, i).Value = entity.OuthosBedCount.ToString();
                    sheet.Cell(startRow + 12, i).Value = entity.XNHBCTotalFee.ToString("F2");
                    sheet.Cell(startRow + 13, i).Value = entity.ClincTotalFee.ToString("F2");
                    sheet.Cell(startRow + 14, i).Value = entity.ClincYPFee.ToString("F2");
                    sheet.Cell(startRow + 15, i).Value = entity.ClincBCFee.ToString("F2");
                    sheet.Cell(startRow + 16, i).Value = entity.ClincCount.ToString();


                }
            }
        }


        public void SetWorksheetC3_1(List<C3_1Entity> entitys, ExcelWorksheet sheet)
        {

            if (sheet != null && entitys.Count > 1)
            {

                List<BZEntity> bzEntitys = ReadXml.GetBZEntity();
                List<AgeEntity> ageEntitys = MedInfo.GetAgeEntity();
                List<MedTypeEntity> medtypeEntitys = MedInfo.GetMedTypeEntity();


                int startRow = 8;

                int index = 0;//实体起始索引
               

                //循环病种
                foreach (BZEntity bzentity in bzEntitys)
                {
                    int startCell = 3;

                    //循环年龄段
                    foreach (AgeEntity item in ageEntitys)
                    {
                        int temp = startCell;
                        
                        //循环人群类别
                        foreach (MedTypeEntity medtypeentity in medtypeEntitys)
                        {
                           
                            C3_1Entity entity = entitys[index++];

                            sheet.Cell(startRow, temp).Value = entity.OutHosCount.ToString();
                            sheet.Cell(startRow + 1, temp).Value = entity.OuthosBedCount.ToString();
                            sheet.Cell(startRow + 2, temp).Value = entity.TotalFee.ToString("F2");
                            sheet.Cell(startRow + 3, temp).Value = entity.YPFee.ToString("F2");
                            sheet.Cell(startRow + 4, temp).Value = entity.JCFee.ToString("F2");
                            sheet.Cell(startRow + 5, temp).Value = entity.ZLFee.ToString("F2");
                            sheet.Cell(startRow + 6, temp).Value = entity.SSFee.ToString("F2");
                            sheet.Cell(startRow + 7, temp).Value = entity.BCFWTotalFee.ToString("F2");
                            sheet.Cell(startRow + 8, temp).Value = entity.BRZFTotalFee.ToString("F2");


                            temp++;
                        }
                        startCell += 4;
                    }
                    startRow += 10;
                }
            }
        }


        public void SetWorksheetC3_2(List<C3_2Entity> entitys, ExcelWorksheet sheet)
        {
            if (sheet != null && entitys.Count>1)
            {

                int startCell = 2;
                int startRow = 8;

                int nextBZType = 0;//下个病种起止

                for (int j = 0; j < 10; j++)
                {
                    //列循环
                    for (int i = startCell; i < 30 + startCell; i++)
                    {

                        C3_2Entity entity = entitys[nextBZType++];
                        //行循环

                        sheet.Cell(startRow, i).Value = entity.DiagnoseID.ToString();

                        sheet.Cell(startRow + 1, i).Value = entity.HYZID.ToString()+" ";
                        sheet.Cell(startRow + 2, i).Value = entity.PatientName.ToString();
                        sheet.Cell(startRow + 3, i).Value = entity.Sex.ToString();
                        sheet.Cell(startRow + 4, i).Value = entity.Age.ToString();
                        sheet.Cell(startRow + 5, i).Value = entity.TotalFee.ToString("F2");
                        sheet.Cell(startRow + 6, i).Value = entity.ZFFee.ToString("F2");
                        sheet.Cell(startRow + 7, i).Value = entity.BedCount.ToString();



                        sheet.Cell(startRow + 9, i).Value = entity.KSSLS.ToString();
                        sheet.Cell(startRow + 10, i).Value = entity.KSSName.ToString();
                        sheet.Cell(startRow + 11, i).Value = entity.JSCount.ToString();
                        sheet.Cell(startRow + 12, i).Value = entity.JSName.ToString();
                        sheet.Cell(startRow + 13, i).Value = entity.YYCount.ToString();
                        sheet.Cell(startRow + 14, i).Value = entity.YYName.ToString();
                        sheet.Cell(startRow + 15, i).Value = entity.YPFee.ToString("F2");
                        sheet.Cell(startRow + 16, i).Value = entity.KSSYPFee.ToString("F2");
                        sheet.Cell(startRow + 17, i).Value = entity.JSYPFee.ToString("F2");
                        sheet.Cell(startRow + 18, i).Value = entity.YYYPFee.ToString("F2");
                        sheet.Cell(startRow + 19, i).Value = entity.OtherYPFee.ToString("F2");



                        sheet.Cell(startRow + 21, i).Value = entity.OtherFZJCFee.ToString("F2");
                        sheet.Cell(startRow + 22, i).Value = entity.YLSBJCFee.ToString("F2");
                        sheet.Cell(startRow + 23, i).Value = entity.YLSBJCCount.ToString();
                        sheet.Cell(startRow + 24, i).Value = entity.YLSBJCYXCount.ToString();
                        sheet.Cell(startRow + 25, i).Value = entity.YLSBCountMaxName.ToString();
                        sheet.Cell(startRow + 26, i).Value = entity.YLSBCountMax.ToString();
                        sheet.Cell(startRow + 27, i).Value = entity.YLSBJCMaxYXCount.ToString();
                        sheet.Cell(startRow + 28, i).Value = entity.YLSBJCMaxFee.ToString("F2");



                    }
                    startRow += 31;

                    // nextBZType += 12;


                }
            }
        }



        public DataSet ReadExcel(string sExcelFile)
        {
            string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Application.StartupPath + "//2011年第1季度监测表格.xls" + ";Extended Properties=Excel 8.0";
            OleDbConnection connection = new OleDbConnection(sConnectionString);
            OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", connection);
            DataSet ds = new DataSet();
            adp.Fill(ds, "Book1");
            return ds;
        }




    }
}
